import pymysql
from utils import get_connection, get_client_ip
from datetime import datetime

def execute_query(query, params=None):
    """执行数据库查询并返回结果"""
    conn = None
    try:
        conn = get_connection()
        with conn.cursor(pymysql.cursors.DictCursor) as cursor:
            cursor.execute(query, params)
            if query.strip().upper().startswith("SELECT"):
                result = cursor.fetchall() if "ALL" in query.upper() else cursor.fetchone()
                return result
            else:
                conn.commit()
                return True  # 对于非查询操作，返回 True 表示操作成功
    except Exception as e:
        print(f"Error: {e}")
        if conn:
            conn.rollback()
        return None
    finally:
        if conn:
            conn.close()


def get_user(username):
    query = "SELECT username, password FROM user WHERE username = %s LIMIT 1"
    return execute_query(query, (username,))


def get_logging():
    conn = get_connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("SELECT * FROM login_data")
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data


def create_table_login_data():
    query = """  
    CREATE TABLE IF NOT EXISTS `login_data` (  
        `id` INT AUTO_INCREMENT PRIMARY KEY,  
        `username` VARCHAR(255) NOT NULL,  
        `time` DATETIME,
        `ip` VARCHAR(255),
        `status` VARCHAR(255)
    );
    """
    return execute_query(query)


def insert_login_data(username,ip_address,status):
    current_time = datetime.now()
    query = "INSERT INTO `login_data` (`username`, `time`, `ip`, `status`) VALUES (%s, %s, %s, %s)"
    return execute_query(query, (username, current_time, ip_address, status))


def get_login_data1():
    query = "SELECT username, time, ip FROM login_data ORDER BY id DESC LIMIT 1"
    return execute_query(query)


def get_information(username):
    query = "SELECT id, username, email FROM information WHERE username = %s LIMIT 1"
    return execute_query(query, (username,))


# 示例调用（通常这些调用会放在另一个脚本或函数中）
if __name__ == "__main__":
    # 假设 utils.get_connection() 能够正确返回数据库连接
    # 这里只是演示如何调用这些函数，并打印结果（在实际应用中，应该避免在函数内部打印）
    print(get_user('some_username'))
    print(get_logging())
    # create_table_login_data()  # 这通常只需要在初始化时调用一次
    insert_login_data('another_username')
    print(get_login_data1())
    print(get_information('some_username'))